﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using DBHelper;
using BusinessLayer.WorldArtPrice.Entities;
using System.Data.SqlClient;
using System.Data;
using BusinessLayer.WorldArtPrice.Models;

namespace BusinessLayer.WorldArtPrice
{
    public class ArtistBL
    {
        private readonly DbHelper _db;

        public ArtistBL()
        {
            _db = new DbHelper(new DbConfig().DbWorldArtPriceConnectionString);
        }

        public List<ArtistModel> Search(string keyWord, int born, int died, int country, int pageIndex, int pageSize)
        {
            var oCommand = new SqlCommand("sp_Artists_Search") { CommandType = CommandType.StoredProcedure };
            oCommand.Parameters.Add(new SqlParameter("@KeyWord", keyWord));
            oCommand.Parameters.Add(new SqlParameter("@Born", born));
            oCommand.Parameters.Add(new SqlParameter("@Died", died));
            oCommand.Parameters.Add(new SqlParameter("@Country", country));
            oCommand.Parameters.Add(new SqlParameter("@PageIndex", pageIndex));
            oCommand.Parameters.Add(new SqlParameter("@PageSize", pageSize));

            var list = _db.GetList<ArtistModel>(oCommand);
            return list;
        }
        public List<ArtistForAdminModel> SearchForAdmin(string keyWord, int born, int died, int country, int pageIndex, int pageSize)
        {
            var oCommand = new SqlCommand(Common.Constants.StoredProcedures.SearchArtistForAdmin) { CommandType = CommandType.StoredProcedure };
            oCommand.Parameters.Add(new SqlParameter("@KeyWord", keyWord));
            oCommand.Parameters.Add(new SqlParameter("@Born", born));
            oCommand.Parameters.Add(new SqlParameter("@Died", died));
            oCommand.Parameters.Add(new SqlParameter("@Country", country));
            oCommand.Parameters.Add(new SqlParameter("@PageIndex", pageIndex));
            oCommand.Parameters.Add(new SqlParameter("@PageSize", pageSize));

            var list = _db.GetList<ArtistForAdminModel>(oCommand);
            return list;
        }
        public void GetTotalRecordsBy(string keyWord, int born, int died, int country, out int totalRecord)
        {
            var oCommand = new SqlCommand(Common.Constants.StoredProcedures.SearchArtistForAdminCount) { CommandType = CommandType.StoredProcedure };
            oCommand.Parameters.Add(new SqlParameter("@KeyWord", keyWord));
            oCommand.Parameters.Add(new SqlParameter("@Born", born));
            oCommand.Parameters.Add(new SqlParameter("@Died", died));
            oCommand.Parameters.Add(new SqlParameter("@Country", country));
            var output = new SqlParameter("@TotalRecord", SqlDbType.Int) { Direction = ParameterDirection.Output };
            oCommand.Parameters.Add(output);
            _db.ExecuteNonQuery(oCommand);
            totalRecord = (int)output.Value;
        }

        public void GetCountByKeyWord(string keyWord, int born, int died, int country, out int totalRecord)
        {
            var oCommand = new SqlCommand("sp_Artists_Select_Count_By_KeyWord") { CommandType = CommandType.StoredProcedure };
            oCommand.Parameters.Add(new SqlParameter("@KeyWord", keyWord));
            oCommand.Parameters.Add(new SqlParameter("@Born", born));
            oCommand.Parameters.Add(new SqlParameter("@Died", died));
            oCommand.Parameters.Add(new SqlParameter("@Country", country));
            var output = new SqlParameter("@TotalRecord", SqlDbType.Int) { Direction = ParameterDirection.Output };
            oCommand.Parameters.Add(output);
            _db.ExecuteNonQuery(oCommand);
            totalRecord = (int)output.Value;
        }

        public Artist GetArtistById(int id)
        {
            var oCommand = new SqlCommand(Common.Constants.StoredProcedures.GetArtistByArtistId) { CommandType = CommandType.StoredProcedure };
            oCommand.Parameters.Add(new SqlParameter("@ArtistId", id));
            var list = _db.GetList<Artist>(oCommand);
            return list.FirstOrDefault();
        }
        public int CreateArtist(Artist artist)
        {
            int result = -1;
            try
            {

                var oCommand = new SqlCommand(Common.Constants.StoredProcedures.InsertArtist) { CommandType = CommandType.StoredProcedure };
                oCommand.Parameters.Add(new SqlParameter("@FullName", artist.FullName));
                oCommand.Parameters.Add(new SqlParameter("@FirstName", artist.FirstName));
                oCommand.Parameters.Add(new SqlParameter("@MiddleName", artist.MiddleName));
                oCommand.Parameters.Add(new SqlParameter("@LastName", artist.LastName));
                if (artist.Born > 0)
                    oCommand.Parameters.Add(new SqlParameter("@Born", artist.Born));
                else
                    oCommand.Parameters.Add(new SqlParameter("@Born", DBNull.Value));
                if (artist.Died > 0)
                    oCommand.Parameters.Add(new SqlParameter("@Died", artist.Died));
                else
                    oCommand.Parameters.Add(new SqlParameter("@Died", DBNull.Value));
                oCommand.Parameters.Add(new SqlParameter("@Country", artist.Country));
                if (artist.BornDiedTextId > 0)
                    oCommand.Parameters.Add(new SqlParameter("@BornDiedTextId", artist.BornDiedTextId));
                else
                    oCommand.Parameters.Add(new SqlParameter("@BornDiedTextId", DBNull.Value));
                oCommand.Parameters.Add(new SqlParameter("@Affiliate_Id", String.IsNullOrEmpty(artist.Affiliate_Id)? DBNull.Value:(object)artist.Affiliate_Id));
                oCommand.Parameters.Add(new SqlParameter("@DateCreated", artist.DateCreated));



                result = _db.ExecuteNonQuery(oCommand);
                return result;
            }
            catch (Exception ex)
            {

            }
            return result;


        }
        public int UpdateArtist(Artist artist)
        {
            int result = -1;
            try
            {

                var oCommand = new SqlCommand(Common.Constants.StoredProcedures.UpdateArtist) { CommandType = CommandType.StoredProcedure };
                oCommand.Parameters.Add(new SqlParameter("@ArtistId", artist.ArtistId));
                oCommand.Parameters.Add(new SqlParameter("@FullName", artist.FullName));

                oCommand.Parameters.Add(new SqlParameter("@FirstName", artist.FirstName));
                oCommand.Parameters.Add(new SqlParameter("@MiddleName", artist.MiddleName));
                oCommand.Parameters.Add(new SqlParameter("@LastName", artist.LastName));
                if (artist.Born > 0)
                    oCommand.Parameters.Add(new SqlParameter("@Born", artist.Born));
                else
                    oCommand.Parameters.Add(new SqlParameter("@Born", DBNull.Value));
                if (artist.Died > 0)
                    oCommand.Parameters.Add(new SqlParameter("@Died", artist.Died));
                else
                    oCommand.Parameters.Add(new SqlParameter("@Died", DBNull.Value));
                oCommand.Parameters.Add(new SqlParameter("@Country", artist.Country));
                if (artist.BornDiedTextId > 0)
                    oCommand.Parameters.Add(new SqlParameter("@BornDiedTextId", artist.BornDiedTextId));
                else
                    oCommand.Parameters.Add(new SqlParameter("@BornDiedTextId", DBNull.Value));
                oCommand.Parameters.Add(new SqlParameter("@Affiliate_Id", String.IsNullOrEmpty(artist.Affiliate_Id) ? DBNull.Value : (object)artist.Affiliate_Id));
               



                result = _db.ExecuteNonQuery(oCommand);
                return result;
            }
            catch (Exception ex)
            {

            }
            return result;


        }
        public int DeleteArtistsByIds(List<string> lstofids)
        {
            int result = -1;

            string ids = "";
            foreach (var id in lstofids)
            {
                ids += id + ",";
            }
            ids = String.IsNullOrEmpty(ids) ? "" : ids.Substring(0, ids.Length - 1);
            if (!String.IsNullOrEmpty(ids))
            {
                try
                {
                    var oCommand = new SqlCommand(String.Format("DELETE FROM Artists WHERE ArtistId IN ({0});", ids)) { CommandType = CommandType.Text };
                    result = _db.ExecuteNonQuery(oCommand);
                    return result;
                }
                catch (Exception ex)
                {

                }
            }
            return result;
        }
    }
}
